package com.ccubee.data.transfer.jdbc;

import cn.hutool.core.collection.CollUtil;
import com.ccubee.data.transfer.event.ColumnData;
import com.ccubee.data.transfer.event.SqlData;
import com.ccubee.data.transfer.properties.transfer.DbProperties;
import com.ccubee.data.transfer.transfer.type.TypeContext;
import com.ccubee.data.transfer.types.ColumnType;
import com.google.common.collect.Lists;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * jdbc连接池 工具类
 *
 * @Author ycf
 * @Date 2022/5/22 17:22
 * @Version 1.0
 */
@Slf4j
public final class JdbcPoolUtil {

    private static void logStart(String sql, List<?> data) {
        if (log.isDebugEnabled()) {
            log.debug("sql start>>>>>>>>>>>>>");
            log.debug("executeSql:{}", sql);
            log.debug("raw sql:{}", String.format(sql.replaceAll("\\?", "%s"), data.toArray()));
        }
    }

    /**
     * 对 sql 语句进行赋值处理
     *
     * @param sql  要执行的 sql
     * @param list 要填充的字符按
     * @throws SQLException sql 异常
     */
    private static PreparedStatement getExecutePps(Connection connection, String sql, List<?> list) throws SQLException {
        PreparedStatement prepareStatement = connection.prepareStatement(sql);
        if (!list.isEmpty()) {
            for (int item = 0; item < list.size(); item++) {
                prepareStatement.setObject(item + 1, list.get(item));
            }
        }
        return prepareStatement;
    }

    /**
     * 执行sql语句
     *
     * @param dataSource 数据源
     * @param sql        sql
     * @param data       数据
     * @return
     */
    @SneakyThrows
    public static boolean executeSql(DataSource dataSource, String sql, List<?> data) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            logStart(sql, data);
            connection = Objects.requireNonNull(dataSource).getConnection();
            preparedStatement = getExecutePps(connection, sql, data);
            count = preparedStatement.executeUpdate();
            if (log.isDebugEnabled()) {
                log.debug("param:{},result:{}", data, count);
                log.debug("sql end<<<<<<<<<<<<<<<<");
            }
        } catch (Exception e) {
            log.error("executeSql error:{},{},{},data:{}", e.getMessage(), sql, data, e);
        } finally {
            if (Objects.nonNull(connection)) {
                Objects.requireNonNull(connection).close();
            }
            if (Objects.nonNull(preparedStatement)) {
                preparedStatement.close();
            }
        }
        return count > 0;
    }

    /**
     * 执行查询的sql
     *
     * @param dataSource  数据源
     * @param sqlData     sql数据
     * @param tableConfig 表配置
     * @return 结果
     */
    public static List<List<ColumnData>> executeQuery(DataSource dataSource,
                                                      SqlData sqlData,
                                                      DbProperties.TableConfig tableConfig) {
        return executeQuery(dataSource, sqlData.getSql(), sqlData.getData(), tableConfig);
    }

    /**
     * 执行sql语句
     *
     * @param dataSource  数据源
     * @param sql         sql
     * @param data        数据
     * @param tableConfig 表格配置
     * @return 数据结果
     */
    @SneakyThrows
    public static List<List<ColumnData>> executeQuery(DataSource dataSource,
                                                      String sql,
                                                      List<?> data,
                                                      DbProperties.TableConfig tableConfig) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        List<List<ColumnData>> list = Lists.newArrayList();
        try {
            logStart(sql, data);
            connection = dataSource.getConnection();
            preparedStatement = getExecutePps(connection, sql, data);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<ColumnData> columnData;
            if (CollUtil.isEmpty(tableConfig.getColumns())) {
                columnData = resultColumn(resultSet);
            } else {
                columnData = tableConfig.getColumns()
                        .stream()
                        .map(i -> ColumnData.builder()
                                .name(Objects.isNull(i.getSource()) ? Objects.requireNonNull(i.getTarget(), "目标字段不能为空") : i.getSource())
                                .columnType(i.getColumType())
                                .columValueName(i.getColumValue())
                                .build())
                        .collect(Collectors.toList());
            }
            while (resultSet.next()) {
                list.add(readResult(columnData, resultSet));
            }
            if (log.isDebugEnabled()) {
                log.debug("config:{}", tableConfig);
                log.debug("sql end<<<<<<<<<<<<<<<<");
            }
            resultSet.close();
        } catch (Exception e) {
            log.error("executeSql error:{},{},{}", e.getMessage(), sql, e);
        } finally {
            if (Objects.nonNull(connection)) {
                Objects.requireNonNull(connection).close();
            }
            if (Objects.nonNull(preparedStatement)) {
                Objects.requireNonNull(preparedStatement).close();
            }
        }
        return list;
    }

    /**
     * 读取 resultset 数据
     *
     * @param columnData 字段
     * @param resultSet  返回结果
     * @return 数据
     */
    @SneakyThrows
    private static List<ColumnData> readResult(List<ColumnData> columnData, ResultSet resultSet) {
       return columnData.stream()
                .map(data -> {
                    if (null != data) {
                        ColumnData columnData1 = ColumnData.builder()
                                .name(data.getName())
                                .sqlType(data.getSqlType())
                                .columnType(data.getColumnType())
                                .columValueName(data.getColumValueName())
                                .build();
                        columnData1.setValue(TypeContext.apply(data, resultSet));
                        return columnData1;
                    }
                    return null;
                }).filter(Objects::nonNull).collect(Collectors.toList());
    }

    @SneakyThrows
    private static Object getObject(ResultSet resultSet, String k) {
        return resultSet.getObject(k);
    }

    @SneakyThrows
    private static String getString(ResultSet resultSet, String k) {
        return resultSet.getString(k);
    }

    /**
     * 获取当前所有字段
     *
     * @param resultSet 结果集
     * @return 字段
     */
    @SneakyThrows
    private static List<ColumnData> resultColumn(ResultSet resultSet) {
        ResultSetMetaData metaData = resultSet.getMetaData();
        List<ColumnData> stringSet = Lists.newArrayList();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            stringSet.add(ColumnData.builder()
                    .columnType(ColumnType.NONE)
                    .indexName(i)
                    .sqlType(metaData.getColumnType(i))
                    .name(metaData.getColumnName(i)).build());
        }
        return stringSet;
    }
}
